package db_layer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import model_layer.Product;
import model_layer.ProductPart;
import model_layer.Restock;

public class RestockDB
{
	private Connection con;
	
	public RestockDB()
	{
		con = DBConnection.get_instance().get_connection();
	}
	
	public Restock find_restock(String var_name, String var, boolean make_association)
	{
		return single_find(var_name, var, -1, make_association);
	}
	
	public Restock find_restock(String var_name, int var, boolean make_association)
	{
		return single_find(var_name, "", var, make_association);
	}
	
	public List<Restock> find_all_restocks(boolean make_association)
	{
		return multiple_find("", "", -1, make_association);
	}
	
	public boolean insert_restock(Restock restock)
	{
		int id = -1;
		boolean correct = false;
		List<ProductPart> products = restock.getProducts();
		Date receivement_date = restock.getReceivement_date();
		float total_price = restock.getTotal_price();
		int sup_id = Integer.parseInt(restock.getSupplier().getId());
		try
		{
			DBConnection.begin_transaction();
			ProductDB prod_db = new ProductDB();
			PreparedStatement restock_ps = DBUtilities.make_insert_statement(con, "Restocks", "receiving_date, total_price, sup_id");
			restock_ps.setDate(1, new java.sql.Date(receivement_date.getTime()));
			restock_ps.setFloat(2, total_price);
			restock_ps.setInt(3, sup_id);
			restock_ps.setQueryTimeout(5);
			restock_ps.executeUpdate();
			restock_ps.close();
			
			id = DBUtilities.get_max(con, "Restocks", "id");
			insert_product_parts(products, id);
			prod_db.update_amounts_and_prices(products);
			DBConnection.end_transaction();
			correct = true;
		}
		catch(SQLException e)
		{
			System.out.println("Error while inserting a restock: " + e);
			DBConnection.rollback_transaction();
		}
		return correct;
	}
	
	public boolean delete_restock(int restock_id)
	{
		boolean success = false;
		try
		{
			PreparedStatement restock_ps = DBUtilities.make_delete_statement(con, "Restocks", "id");
			restock_ps.setInt(1, restock_id);
			restock_ps.setQueryTimeout(5);
			restock_ps.executeUpdate();
			success = true;
			
		}
		catch(SQLException e)
		{
			System.err.println("Error while deleting a restock: " + e);
		}
		return success;
	}
	
	private void insert_product_parts(List<ProductPart> products, int id) throws SQLException
	{
		int number_of_products = products.size();
		PreparedStatement product_part_ps = DBUtilities.make_multiple_insert_statement(con, "ProductParts",
				"prod_id, restock_id, retail_price, amount, total_price", number_of_products);
		int n = 0;
		for (ProductPart product : products)
		{
			product_part_ps.setInt(1 + 5 * n, Integer.parseInt(product.getProduct().getId()));
			product_part_ps.setInt(2 + 5 * n, id);
			product_part_ps.setFloat(3 + 5 * n, product.getRetail_price());
			product_part_ps.setInt(4 + 5 * n, product.getAmount());
			product_part_ps.setFloat(5 + 5 * n, product.getTotal_price());
			n++;
		}
		
		product_part_ps.setQueryTimeout(5);
		product_part_ps.executeUpdate();
		product_part_ps.close();
	}
	
	
	private Restock single_find(String var_name, String s_var, int i_var, boolean make_association)
	{
		Restock res = null;
		ResultSet results;
		ResultSet prod_results;
		String query = make_restock_query();
		try
		{
			DBConnection.begin_transaction();
			PreparedStatement ps = DBUtilities.prepare_statement(con, query, var_name, s_var, i_var);
			ps.setQueryTimeout(5);
			results = ps.executeQuery();
			if(results.next())
			{
				res = create_restock(results);
				if(make_association)
				{
					query = make_product_parts_query();
					PreparedStatement prod_ps = DBUtilities.prepare_statement(con, query, "restock_id", res.getId());
					prod_ps.setQueryTimeout(5);
					prod_results = prod_ps.executeQuery();
					List<ProductPart> products = create_product_parts_list(prod_results);
					res.setProducts(products);
				}
			}
			ps.close();
			DBConnection.end_transaction();
		}
		catch(SQLException e)
		{
			System.err.println("Error while searching for restock: " + e);
			DBConnection.rollback_transaction();
		}
		return res;
	}
	
	private List<Restock> multiple_find(String var_name, String s_var, int i_var, boolean make_association)
	{
		Restock res;;
		List<Restock> restocks = new ArrayList<Restock>();
		ResultSet results;
		ResultSet prod_results;
		String query = make_restock_query();
		try
		{
			DBConnection.begin_transaction();
			PreparedStatement ps = DBUtilities.prepare_statement(con, query, var_name, s_var, i_var);
			ps.setQueryTimeout(5);
			results = ps.executeQuery();
			while(results.next())
			{
				res = create_restock(results);
				if(make_association)
				{
					query = make_product_parts_query();
					PreparedStatement prod_ps = DBUtilities.prepare_statement(con, query, "restock_id", res.getId());
					prod_ps.setQueryTimeout(5);
					prod_results = prod_ps.executeQuery();
					List<ProductPart> products = create_product_parts_list(prod_results);
					res.setProducts(products);
				}
				restocks.add(res);
			}
			ps.close();
			DBConnection.end_transaction();
		}
		catch(SQLException e)
		{
			restocks.clear();
			System.err.println("Error while searching for restocks: " + e);
			DBConnection.rollback_transaction();
		}
		return restocks;
	}
	
	private Restock create_restock(ResultSet results)
	{
		try
		{
			Restock r = new Restock();
			r.setId(results.getInt("id"));
			SupplierDB sup_db = new SupplierDB();
			r.setSupplier(sup_db.find_supplier("id", results.getInt("sup_id")));
			Date receivement_date = new Date(results.getDate("receiving_date").getTime());
			r.setReceivement_date(receivement_date);
			r.setTotal_price(results.getFloat("total_price"));
			return r;
		}
		catch(SQLException e)
		{
			System.err.println("Error while creating a restock: " + e);
			return null;
		}
	}
	
	private List<ProductPart> create_product_parts_list(ResultSet results)
	{
		List<ProductPart> products = new ArrayList<ProductPart>();
		ProductPart prod;
		try
		{
			while(results.next())
			{
				prod = new ProductPart();
				prod.setAmount(results.getInt("amount"));
				prod.setRetail_price(results.getFloat("retail_price"));
				prod.setTotal_price(results.getFloat("total_price"));
				ProductDB prod_db = new ProductDB();
				Product product = prod_db.find_product("id", results.getInt("prod_id"));
				if(product == null)
				{
					SQLException e = new SQLException();
					throw e;
				}
				prod.setProduct(product);
				products.add(prod);
			}
		}
		catch(SQLException e)
		{
			System.err.println("Error while creating product part list: " + e);
			products.clear();
		}
		return products;
	}
	

	
	private String make_restock_query()
	{
		return DBUtilities.make_query("id, sup_id, receiving_date, total_price", "Restocks");
	}
	
	private String make_product_parts_query()
	{
		return DBUtilities.make_query("prod_id, restock_id, retail_price, amount, total_price", "ProductParts");
	}

}
